package system.base;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import system.base.InOutStatisticSearchClass.FirstForm;

import framework.core.*;

/*
 Author    :Wing
 CreateTime:2008-02-20 17:30:49
 */
public class InOutStatistic {

    public InOutStatistic() {
        
    }

    public static InOutStatistic fetch() {
        return new InOutStatistic();
    }

    // ----add by Wing start----//
    public static boolean IsCheckedOfWarehouseCode(String loginWarehouseCode, String warehouseCode) throws Exception {
        boolean checked = false;
        if (loginWarehouseCode != null) {
            if (loginWarehouseCode.equals(warehouseCode))
                return true;
        }
        return checked;
    }

    public ObjectList detailqueryGroup(DBConnection con, InOutStatisticSearchEvent event) {
        /**正式提取开始*/
        ObjectList mReturn = ObjectList.getList();
//        String sumIn = "select sum(b.product_money) from t_in_head a,t_in_sub b ";
//        String sql1 = "select * from (";
//        String sql2 = "select " +
//        		"a.warehouse_code,b.position_code,upper(b.product_code) product_code,b.product_price " +
//                "from t_in_head a,t_in_sub b " ;
//        if (!event.getProviderName().equalsIgnoreCase("")) {
//            sql2 += " ,t_product e,t_provider f ";
//            sumIn += " ,t_product e,t_provider f ";
//        }else{
//            if (!event.getIsNormal().equalsIgnoreCase("")) {
//                sql2 += " ,t_product e ";
//                sumIn += " ,t_product e ";
//            }
//        }
//        String sql3 = "where " +
//                "a.IN_BILL_CODE=b.IN_BILL_CODE ";
//        if (!event.getProviderName().equalsIgnoreCase("")) {
//            sql3 += "and a.PROVIDER_CODE = f.CODE ";
//        }
//        if (!event.getIsNormal().equalsIgnoreCase("")) {
//            sql3 += " and upper(b.PRODUCT_CODE) = e.CONCENTRICITY_CODE  ";
//        }
//        if (event.getWarehouseCodes() != null) {
//            sql3 += " and a.warehouse_code = (";
//            for (int i = 0; i < event.getWarehouseCodes().length; i++) {
//                if (i > 0)
//                    sql3 += ",";
//                sql3 += "'" + event.getWarehouseCodes()[i] + "'";
//            }
//            sql3 += ")";
//        }
//        if (!event.getProviderName().equalsIgnoreCase("")) {
//            sql3 += " and f.name = '" + event.getProviderName() + "'";
//            sql3 += " and f.CODE = e.PROVIDER_CODE and e.CONCENTRICITY_CODE = upper(b.PRODUCT_CODE) ";
//        }
//        if (!event.getBalanceFlag().equalsIgnoreCase("")) {
//            sql3 += " and a.balance_flag = '" + event.getBalanceFlag() + "'";
//        }
//        if (!event.getStartStatisticTime().equalsIgnoreCase("")) {
//            sql3 += " and a.in_time >= '" + event.getStartStatisticTime() + " 00:00:00'";
//        }
//        if (!event.getEndStatisticTime().equalsIgnoreCase("")) {
//            sql3 += " and a.in_time <= '" + event.getEndStatisticTime() + " 23:59:59'";
//        }
//        if (!event.getIsNormal().equalsIgnoreCase("")) {
//            sql3 += " and e.is_normal = '" + event.getIsNormal() + "'";
//        }
//        if (!event.getBalanceMode().equalsIgnoreCase("")) {
//            sql3 += " and b.balance_mode = '" + event.getBalanceMode() + "'";
//        }
//        if (event.getWarehouseCodeUser().length() > 0) {
//            sql3 += " and a.WAREHOUSE_CODE like '" + event.getWarehouseCodeUser() + "%'";
//        }
//        if(event.getProductCode().length() > 0){
//            sql3 += " and upper(b.PRODUCT_CODE) like '%"+event.getProductCode().toUpperCase()+"%'";
//        }
//        String sql4= "group by a.warehouse_code,b.position_code,upper(b.product_code),b.product_price ";
//        String sql5 = "union ";
//        String sumOut = "select sum(d.product_money) from t_out_head c,t_out_sub d ";
//        String sql6 = "select " +
//        		"c.warehouse_code,d.position_code, upper(d.product_code) product_code,d.product_price " +
//                "from t_out_head c,t_out_sub d ";
//        if (!event.getProviderName().equalsIgnoreCase("")) {
//            sql6 += "  ,t_product e ,t_provider f ";
//            sumOut += "  ,t_product e ,t_provider f ";
//        }else{
//            if (!event.getIsNormal().equalsIgnoreCase("")) {
//                sql6 += " ,t_product e ";
//                sumOut += " ,t_product e ";
//            }
//        }
//        String sql7 = "where " +
//                "c.out_BILL_CODE=d.out_BILL_CODE ";
////        if (!event.getProviderName().equalsIgnoreCase("")&&!event.getIsNormal().equalsIgnoreCase("")) {
////            sql7 += "and e.PROVIDER_CODE = f.CODE ";
////        }
//        if (!event.getIsNormal().equalsIgnoreCase("")) {
//            sql7 += " and upper(d.PRODUCT_CODE) = e.CONCENTRICITY_CODE  ";
//        }
//        if (event.getWarehouseCodes() != null) {
//            sql7 += " and c.warehouse_code in (";
//            for (int i = 0; i < event.getWarehouseCodes().length; i++) {
//                if (i > 0)
//                    sql7 += ",";
//                sql7 += "'" + event.getWarehouseCodes()[i] + "'";
//            }
//            sql7 += ")";
//        }
//        if (!event.getProviderName().equalsIgnoreCase("")) {
//            sql7 += " and f.name = '" + event.getProviderName() + "'";
//            sql7 += " and f.CODE = e.PROVIDER_CODE and e.CONCENTRICITY_CODE = upper(d.PRODUCT_CODE) ";
//        }
//        if (!event.getBalanceFlag().equalsIgnoreCase("")) {
//            sql7 += " and c.balance_flag = '" + event.getBalanceFlag() + "'";
//        }
//        if (!event.getStartStatisticTime().equalsIgnoreCase("")) {
//            sql7 += " and c.out_time >= '" + event.getStartStatisticTime() + " 00:00:00'";
//        }
//        if (!event.getEndStatisticTime().equalsIgnoreCase("")) {
//            sql7 += " and c.out_time <= '" + event.getEndStatisticTime() + " 23:59:59'";
//        }
//        if (!event.getIsNormal().equalsIgnoreCase("")) {
//            sql7 += " and e.is_normal = '" + event.getIsNormal() + "'";
//        }
//        if (!event.getBalanceMode().equalsIgnoreCase("")) {
//            sql7 += " and d.balance_mode = '" + event.getBalanceMode() + "'";
//        }
//        if (event.getWarehouseCodeUser().length() > 0) {
//            sql7 += " and c.WAREHOUSE_CODE like '" + event.getWarehouseCodeUser() + "%'";
//        }
//        if(event.getProductCode().length() > 0){
//            sql7 += " and upper(d.PRODUCT_CODE) like '%"+event.getProductCode().toUpperCase()+"%'";
//        }
//        String sql8 = "group by c.warehouse_code,d.position_code,upper(d.product_code),d.product_price";
//        String sql9 = ")order by product_code "; 
//        String sql = sql1 + sql2 + sql3 + sql4 + sql5 + sql6+ sql7 + sql8 + sql9;
//        sumOut = sumOut + sql7;
//        sumIn = sumIn + sql3;
        
        

        String[] warehouse_code = event.getWarehouseCodes();
        String provider_name = event.getProviderName();
        String start = event.getStartStatisticTime();
        String end = event.getEndStatisticTime();
        String balance_mode = event.getBalanceMode();
        String balance_flag = event.getBalanceFlag();
        String concentricity_code = event.getProductCode();
        String is_normal = event.getIsNormal();
        
        String masterStart = "";
        String masterEnd = "";
        String in = "";
        String out = "";
        
        String warehouseCodes = "";
        
        
        for (int i = 0; i < warehouse_code.length; i++) {
            if(warehouse_code.length == 1){
                warehouseCodes += "'"+warehouse_code[i]+"'";
                break;
            }
            if(i == warehouse_code.length - 1 && warehouse_code.length != 1){
                warehouseCodes += "'"+warehouse_code[i]+"'";
                break;
            }
            warehouseCodes += "'"+warehouse_code[i]+"',";
        }
        masterStart = 
            " select "+   
                " sel_1.warehouse_name,  "+  
                " sel_1.position_name, "+
                " sel_1.product_code, "+
                " pro.name product_name, "+
                " pro.product_unit, "+
                " pro.spec, "+
                " pro.model, "+
                " pro.product_brand, "+
                " per.code provider_code, "+
                " per.name provider_name, "+
                " sel_1.price, "+
                " sum(sel_1.in_count) in_count, "+
                " sum(sel_1.in_moneys) in_moneys, "+
                " sum(sel_1.out_count) out_count, "+
                " sum(sel_1.out_moneys) out_moneys, "+
                " sum(sel_1.in_count) - sum(sel_1.out_count) product_storage_count, "+
                " sum(sel_1.in_moneys) - sum(sel_1.out_moneys) product_storage_moneys "+
                " from ( ";
    masterEnd = 
        " )   sel_1,t_product pro,t_provider per "+
" where "+
        " 1 = 1 "+
        " and  sel_1.warehouse_code in ("+warehouseCodes+") "+  
        " and  sel_1.product_id = pro.id "+  
        " and  per.code = pro.provider_code "+  
        " group by "+  
        " sel_1.warehouse_name, "+
        " sel_1.position_name, "+
        " sel_1.product_code, "+
        " pro.name, "+ 
        " pro.product_unit, "+
        " pro.spec,pro.model, "+
        " pro.product_brand, "+
        " per.code,per.name, "+
        " sel_1.price ";
    in = " select "+  
        " inhead.warehouse_code warehouse_code, "+
        " inhead.warehouse_name warehouse_name, "+
        " insub.product_code product_code, "+  
        " insub.product_id  product_id, "+  
        " insub.position_code position_code, "+
        " insub.position_name position_name, "+
        " insub.product_count in_count, "+  
        " insub.product_price price, "+  
        " insub.product_money in_moneys, "+  
        " 0 out_count, "+  
        " 0 out_moneys, "+  
        " 0 product_storage_count, "+  
        " 0 product_storage_moneys "+  
        " from "+  
        " t_in_sub insub "+  
        " left outer join   t_in_head inhead "+   
        " on   insub.in_bill_id = inhead.id "+  
        " left outer join   t_product pro "+   
        " on   insub.product_id = pro.id "+  
        " left outer join   t_provider per "+   
        " on   per.code = inhead.provider_code "+  
        " where "+  
        " 1 = 1 "+
        " and insub.status = '" + InSub.STATUS_AVALIBLE + "'" + 
        " and inhead.warehouse_code  in ("+warehouseCodes+") ";
    if(provider_name != "")
        in += " and per.name like '%"+provider_name+"%' "; 
    if(start != "")
        in += " and inhead.in_time >= '"+start+"' ";
    if(end != ""){
        if(end.length() <= 10) end += " 23:59:59";
        in += " and inhead.in_time <= '"+end+"' ";
    }    
    if(balance_mode != "")
        in += " and insub.balance_mode = '"+balance_mode+"' ";
    if(balance_flag != "")
        in += " and inhead.balance_flag = '"+balance_flag+"' ";
    if(concentricity_code != "")
        in += " and pro.concentricity_code = '"+concentricity_code+"' ";
    if(is_normal != "")
        in += " and pro.is_normal = '"+is_normal+"' ";
    out = " select "+  
        " outhead.warehouse_code warehouse_code, "+
        " outhead.warehouse_name warehouse_name, "+
        " outsub.product_code product_code, "+  
        " outsub.product_id product_id, "+ 
        " outsub.position_code position_code, "+
        " outsub.position_name position_name, "+  
        " 0 in_count, "+  
        " outsub.product_price price, "+  
        " 0 in_moneys, "+  
        " outsub.product_count out_count, "+  
        " outsub.product_money out_moneys, "+  
        " 0 product_storage_count, "+  
        " 0 product_storage_moneys "+  
        " from  t_out_sub outsub "+  
        " left outer join   t_out_head outhead "+   
        " on   outsub.out_bill_code = outhead.out_bill_code "+  
        " left outer join   t_product pro "+   
        " on   outsub.product_id = pro.id "+  
        " left outer join   t_provider per "+   
        " on   per.code = pro.provider_code "+  
        " where "+  
        " 1 = 1 "+ 
        " and outsub.status = '" + OutSub.STATUS_AVALIBLE + "'" +
        " and  "+
        " outhead.warehouse_code  in ("+warehouseCodes+") ";
    if(provider_name != "")
        out += " and per.name like '%"+provider_name+"%' ";
    if(start != "")
        out += " and outhead.out_time >= '"+start+"' ";
    if(end != ""){
        if(end.length() <= 10) end += " 23:59:59";
        out += " and outhead.out_time <= '"+end+"' ";
    }    
    if(balance_mode != "")
        out += " and outsub.balance_mode = '"+balance_mode+"' ";
    if(balance_flag != "")
        out += " and outhead.balance_flag = '"+balance_flag+"' ";
    if(concentricity_code != "")
        out += " and pro.concentricity_code = '"+concentricity_code+"' ";
    if(is_normal != "")
        out += " and pro.is_normal = '"+is_normal+"' ";
    
   String all = masterStart + in + " union all " + out + masterEnd;
   
   
        try {
            UniversalTable ut = new UniversalTable();
            InOutStatisticSearchClass io = new InOutStatisticSearchClass();
            FirstForm obj = io.selectMoney(con,event);
            ut.initial(con, all, new Conditions(), event.getPageSize(), event.getPageNum());
            mReturn = ut.getList();
            mReturn.add(obj);
        } catch (Exception e) {
            System.out.println("InOutStatistic SQL detailqueryGroup error:" + e.getMessage());
        }
//        UniversalRow row = null; try {
//            row = (UniversalRow)mReturn.get(0);
//        } catch (Exception e) {
//            
//            e.printStackTrace();
//        }
//        if(row.getString("WAREHOUSE_NAME") == null){
//            String w = row.getString("WAREHOUSE_NAME");
//        }
//        if(row.getString("PRICE") == null){
//            String p = row.getString("PRICE");
//        }
        return mReturn;
        /**正是提取结束*/
    }
    //----add by Wing end----//
    public ObjectList detailqueryGroupForInit(DBConnection con, InOutStatisticSearchEvent event,String isFisrtVisit) throws Exception {
        /**正式提取开始*/
        ObjectList mReturn = ObjectList.getList();
        
        String[] warehouse_code = event.getWarehouseCodes();
        String provider_name = event.getProviderName();
        String start = event.getStartStatisticTime();
        String end = event.getEndStatisticTime();
        String balance_mode = event.getBalanceMode();
        String balance_flag = event.getBalanceFlag();
        String concentricity_code = event.getProductCode();
        String is_normal = event.getIsNormal();
        String productName=event.getProductName();
        String productBrand=event.getProductBrand();
        
        String masterStart = "";
        String inFirst = "";
        String inSecond = "";
        String outFirst = "";
        String outSecond = "";
        String warehouseCodes = "" ;
        String[] warehouseCodesSplit;


        //如果统计开始和结束时间都为空
        if(start == "" && end == ""){
        	return this.detailqueryGroupWithNoTime(con, event,isFisrtVisit);
        }else if(start != "" && end =="" || start != "" && end !=""){
            if(warehouse_code.length == 1&& !warehouse_code[0].equals("--1")){
            	warehouseCodesSplit = warehouse_code[0].split(",");
            	if(warehouseCodesSplit.length==1){
            		if(isFisrtVisit.equals("true")){
            			warehouseCodes += "'"+warehouse_code[0]+"'";
            		}else{
            			String warehouseObjectListService = Warehouse.fetchWarehousesForString(con, warehouse_code[0]);
            			String[] service = warehouseObjectListService.split(",");
            			warehouseCodes="";
            	    	for (int i = 0; i < service.length; i++) {
            	    		warehouseCodes +="'"+service[i]+"',";
            			}
            	    	warehouseCodes=warehouseCodes.substring(0,warehouseCodes.length()-1);
            		}
            	}else{
            		for (int j = 0; j < warehouseCodesSplit.length; j++) {
    	        		String warehouseObjectListService = Warehouse.fetchWarehousesForString(con, warehouseCodesSplit[j]);
        				String[] service = warehouseObjectListService.split(",");
        				for (int i = 0; i < service.length; i++) {
        					warehouseCodes +="'"+service[i]+"',";
        				}
    				}
    	        	warehouseCodes = warehouseCodes.substring(0,warehouseCodes.length()-1); 
            	}
            }else{
            	return mReturn;
            }
        	 masterStart = "SELECT a.PRODUCT_CODE,a.PRODUCT_NAME,a.spec,a.model,a.product_brand,a.product_unit,a.product_price," 
        		+" a.provider_code,a.provider_name,sum(a.init_count) init_count,sum(a.init_money) init_money," 
        		+" sum(a.in_count) in_count,sum(a.in_money) in_money,sum(a.out_count) out_count,sum(a.out_money) out_money," 
        		+" sum(a.final_count) final_count,sum(a.final_money) final_money " 
        		+" FROM (SELECT aaa1.*, aaa2.concentricity_code PRODUCT_CODE, aaa2.NAME PRODUCT_NAME, aaa2.spec,"
 				+" aaa2.model, aaa2.product_brand, aaa2.product_unit,per.code provider_code, per.NAME provider_name"
 				+" FROM (SELECT   aa1.WAREHOUSE_CODE,aa1.POSITION_CODE,aa1.product_id pro_id, aa1.product_price,"
 				+" SUM (aa1.init_count) init_count,"
 				+" SUM (aa1.init_money) init_money, SUM (aa1.in_count) in_count,"
 				+" SUM (aa1.in_money) in_money, SUM (aa1.out_count) out_count,"
 				+" SUM (aa1.out_money) out_money,"
                	+" SUM (aa1.init_count)"
                	+" + SUM (aa1.in_count)"
                	+" - SUM (aa1.out_count) final_count,"
                 +" SUM (aa1.init_money)"
                 +" + SUM (aa1.in_money)"
                 +" - SUM (aa1.out_money) final_money"
                 +" FROM (SELECT  sub_month.WAREHOUSE_CODE,sub_month.POSITION_CODE, sub_month.product_id, sub_month.product_price,"
                 +" SUM (sub_month.son_in_count)"
                 +" - SUM (sub_month.son_out_count) init_count,"
                 +" SUM (sub_month.son_in_money)"
                 +" - SUM (sub_month.son_out_money) init_money,"
                 +" 0 in_count, 0 in_money, 0 out_count, 0 out_money"
                 +" FROM (SELECT  son_inhead.WAREHOUSE_CODE,"
                 +" son_insub.POSITION_CODE,"
                 +" son_insub.product_id,"
                 +" son_insub.product_price,"
                 +" 0 son_init_count, 0 son_init_money,"
                 +" SUM(son_insub.product_count) son_in_count,"
                 +" SUM(son_insub.product_money) son_in_money,"
                 +" 0 son_out_count, 0 son_out_money FROM t_in_sub son_insub LEFT OUTER JOIN t_in_head son_inhead"
                 +" ON son_insub.in_bill_id = son_inhead.ID WHERE 1 = 1  AND son_insub.status = '1'";
        	 
        	 inFirst += " and son_inhead.in_time < '"+start+"' ";	 
        	 inFirst += " and son_inhead.warehouse_code  in ("+warehouseCodes+") ";
        	 if(balance_mode != "")
        		inFirst += " and son_insub.balance_mode = '"+balance_mode+"' ";
        	 if(balance_flag != "")
        	    inFirst += " and son_inhead.balance_flag = '"+balance_flag+"' ";
        	 inFirst += " GROUP BY son_inhead.WAREHOUSE_CODE,"
                      + " son_insub.POSITION_CODE,"
                      + " son_insub.product_id,"
                      + " son_insub.product_price";
        	 outFirst += " SELECT   son_outhead.WAREHOUSE_CODE,"
                         +" son_outsub.POSITION_CODE,son_outsub.product_id,"
                         +" son_outsub.product_price,"
                         +" 0 son_init_count, 0 son_init_money,"
                         +" 0 son_in_count, 0 son_in_money,"
                         +" SUM(son_outsub.product_count) son_out_count,"
                         +" SUM(son_outsub.product_money) son_out_money"
                         +" FROM t_out_sub son_outsub LEFT OUTER JOIN t_out_head son_outhead"
                         +" ON son_outsub.out_bill_id = son_outhead.ID WHERE 1 = 1 and son_outsub.status = '1' ";
        	 outFirst += " and son_outhead.out_time < '"+start+"' ";
        	 outFirst += " and son_outhead.warehouse_code in ("+warehouseCodes+")";		
        	 if(balance_mode != "")
        		 outFirst += " and son_outsub.balance_mode = '"+balance_mode+"' ";
         	 if(balance_flag != "")
         		outFirst += " and son_outhead.balance_flag = '"+balance_flag+"' ";
         	
         	outFirst += " GROUP BY son_outhead.WAREHOUSE_CODE,"
         				+ " son_outsub.POSITION_CODE,son_outsub.product_id,"
                        + " son_outsub.product_price ) sub_month"
                        + " GROUP BY sub_month.WAREHOUSE_CODE,sub_month.POSITION_CODE,sub_month.product_id, sub_month.product_price";
         	inSecond += " SELECT   inhead.WAREHOUSE_CODE,insub.POSITION_CODE,"
                        +" insub.product_id, insub.product_price,"
                        +" 0 init_count, 0 init_money,"
                        +" SUM (insub.product_count) in_count,"
                        +" SUM (insub.product_money) in_money, 0 out_count,"
                        +" 0 out_money"
                        +" FROM t_in_sub insub LEFT OUTER JOIN t_in_head inhead"
                        +" ON insub.in_bill_id = inhead.ID WHERE 1 = 1 AND insub.status = '1'";
         	inSecond += " and inhead.in_time >= '"+start+"' ";
         	if(end != ""){
                if(end.length() <= 10) end += " 23:59:59";
                inSecond += " and inhead.in_time <= '"+end+"' ";
            }    
         	inSecond += " and inhead.warehouse_code  in ("+warehouseCodes+") ";
         	if(balance_mode != "")
         		inSecond += " and insub.balance_mode = '"+balance_mode+"' ";
         	if(balance_flag != "")
         		inSecond += " and inhead.balance_flag = '"+balance_flag+"' ";
         	inSecond+=" GROUP BY inhead.WAREHOUSE_CODE,insub.POSITION_CODE,insub.product_id, insub.product_price ";
         	
         	outSecond += "SELECT   outhead.WAREHOUSE_CODE,outsub.POSITION_CODE,outsub.product_id, outsub.product_price,"
                        +" 0 init_count, 0 init_money, 0 in_count, 0 in_money,"
                        +" SUM (outsub.product_count) out_count,"
                        +" SUM (outsub.product_money) out_money"
                        +" FROM t_out_sub outsub LEFT OUTER JOIN t_out_head outhead"
                        +" ON outsub.out_bill_id = outhead.ID"
                        +" LEFT OUTER JOIN t_product product"
                        +" ON outsub.product_id = product.ID WHERE 1 = 1  AND outsub.status = '1' ";
         	outSecond += " and outhead.out_time >= '"+start+"' ";
         	if(end != ""){
                if(end.length() <= 10) end += " 23:59:59";
                outSecond += " and outhead.out_time <= '"+end+"' ";
            }
         	outSecond += " and outhead.warehouse_code  in ("+warehouseCodes+") ";
         	if(balance_mode != "")
         		outSecond += " and outsub.balance_mode = '"+balance_mode+"' ";
         	if(balance_flag != "")
         		outSecond += " and outhead.balance_flag = '"+balance_flag+"' ";
         	outSecond +=" GROUP BY outhead.WAREHOUSE_CODE,outsub.POSITION_CODE,outsub.product_id, outsub.product_price) aa1"
         				+" GROUP BY aa1.WAREHOUSE_CODE,aa1.POSITION_CODE,aa1.product_id, aa1.product_price) aaa1,"
					    +" t_product aaa2,"
					    +" t_provider per"
					    +" WHERE aaa1.pro_id = aaa2.ID"
					    +" AND per.code = aaa2.provider_code"
					    +" AND (  ABS (aaa1.init_count)"
					    +" + ABS (aaa1.in_count)"
					    +" + ABS (aaa1.out_count)"
					    +" + ABS (aaa1.final_count)"
					    +" ) > 0";
         	if(provider_name != "")
         		outSecond += " and per.name like '%"+provider_name+"%' "; 
         	if(concentricity_code != "")
         		outSecond += " and aaa2.concentricity_code = '"+concentricity_code+"' ";
         	if(productName != "")
         		outSecond += " and aaa2.name like '%"+productName+"%' ";
         	if(productBrand != "")
         		outSecond += " and aaa2.product_brand like '%"+productBrand+"%' ";
            if(is_normal != "")
            	outSecond += " and aaa2.is_normal = '"+is_normal+"' ";
            	
            outSecond +=" ) a group by a.PRODUCT_CODE,a.PRODUCT_NAME,a.spec,a.model,a.product_brand,a.product_unit,a.provider_code,a.provider_name,a.product_price ";
            String all = masterStart + inFirst + " union all " + outFirst+" union all "+ inSecond + " union all " + outSecond;
           try {
                UniversalTable ut = new UniversalTable();
                InOutStatisticSearchClass io = new InOutStatisticSearchClass();
                FirstForm obj = io.selectMoneyForAll(con,event,isFisrtVisit,warehouseCodes);
                ut.initial(con, all, new Conditions(), event.getPageSize(), event.getPageNum());
                mReturn = ut.getList();
                mReturn.add(obj);
                return mReturn;
            } catch (Exception e) {
                System.out.println("InOutStatistic SQL detailqueryGroup error:" + e.getMessage());
            }
        }else{
        	//TODO 如果只有末尾时间应该抛出错误
        	this.detailqueryGroup(con, event);
        }
        return mReturn;
        /**正是提取结束*/
    }
    
    public ObjectList detailqueryGroupWithNoTime(DBConnection con, InOutStatisticSearchEvent event,String isFirstVisit) throws Exception {
        /**正式提取开始*/
        ObjectList mReturn = ObjectList.getList();              

        String[] warehouse_code = event.getWarehouseCodes();
        String provider_name = event.getProviderName();
        String start = event.getStartStatisticTime();
        String end = event.getEndStatisticTime();
        String balance_mode = event.getBalanceMode();
        String balance_flag = event.getBalanceFlag();
        String concentricity_code = event.getProductCode();
        String productName = event.getProductName();
        String productBrand = event.getProductBrand();
        String is_normal = event.getIsNormal();
        String masterStart = "";
        String masterEnd = "";
        String in = "";
        String out = ""; 
        String warehouseCodes = "";   
        String[] warehouseCodesSplit;

        if(warehouse_code.length == 1&&!warehouse_code[0].equals("--1")){
        	warehouseCodesSplit = warehouse_code[0].split(",");
        	if(warehouseCodesSplit.length==1){
        			if(isFirstVisit.equals("true")){
        				warehouseCodes += "'"+warehouse_code[0]+"'";
        			}else{
        				String warehouseObjectListService = Warehouse.fetchWarehousesForString(con, warehouse_code[0]);
        				String[] service = warehouseObjectListService.split(",");
        				warehouseCodes="";
        				for (int i = 0; i < service.length; i++) {
        					warehouseCodes +="'"+service[i]+"',";
        				}
        				warehouseCodes = warehouseCodes.substring(0,warehouseCodes.length()-1); 
        			}
        	}else{
        		for (int j = 0; j < warehouseCodesSplit.length; j++) {
	        		String warehouseObjectListService = Warehouse.fetchWarehousesForString(con, warehouseCodesSplit[j]);
    				String[] service = warehouseObjectListService.split(",");
    				for (int i = 0; i < service.length; i++) {
    					warehouseCodes +="'"+service[i]+"',";
    				}
				}
	        	warehouseCodes = warehouseCodes.substring(0,warehouseCodes.length()-1); 
        	}
        }else{
        	return mReturn;
        }

        masterStart = 
            "select a.product_code,a.product_name,a.product_unit,a.spec,a.model,a.product_brand,a.provider_code,a.provider_name," +
            "a.PRODUCT_PRICE,sum(a.in_count) in_count,sum(a.in_money) in_money,sum(a.out_count) out_count,sum(a.out_money) out_money, " +
            "sum(a.final_count) final_count,sum(a.final_money) final_money from ( select "+   
                " sel_1.warehouse_code,  "+  
                " sel_1.position_code, "+
                " sel_1.product_code, "+
                " pro.name product_name, "+
                " pro.product_unit, "+
                " pro.spec, "+
                " pro.model, "+
                " pro.product_brand, "+
                " per.code provider_code, "+
                " per.name provider_name, "+
                " sel_1.price PRODUCT_PRICE, "+
                " sum(sel_1.in_count) in_count, "+
                " sum(sel_1.in_moneys) in_money, "+
                " sum(sel_1.out_count) out_count, "+
                " sum(sel_1.out_moneys) out_money, "+
                " sum(sel_1.in_count) - sum(sel_1.out_count) final_count, "+
                " sum(sel_1.in_moneys) - sum(sel_1.out_moneys) final_money "+
                " from ( ";
    masterEnd = 
        " )   sel_1,t_product pro,t_provider per "+
" where "+
        " 1 = 1 "+
        " and  sel_1.warehouse_code in ("+warehouseCodes+") "+  
        " and  sel_1.product_id = pro.id "+  
        " and  per.code = pro.provider_code "+  
        " group by "+  
        " sel_1.warehouse_code, "+
        " sel_1.position_code, "+
        " sel_1.product_code, "+
        " pro.name, "+ 
        " pro.product_unit, "+
        " pro.spec,pro.model, "+
        " pro.product_brand, "+
        " per.code,per.name, "+
        " sel_1.price ) a group by a.product_code,a.product_name,a.product_unit,a.spec,a.model,a.product_brand,a.provider_code,a.provider_name,a.PRODUCT_PRICE ";
    in = " select "+  
        " inhead.warehouse_code warehouse_code, "+
        " insub.product_code product_code, "+  
        " insub.product_id  product_id, "+  
        " insub.position_code position_code, "+
        " insub.product_count in_count, "+  
        " insub.product_price price, "+  
        " insub.product_money in_moneys, "+  
        " 0 out_count, "+  
        " 0 out_moneys, "+  
        " 0 final_count, "+  
        " 0 final_money "+  
        " from "+  
        " t_in_sub insub "+  
        " left outer join   t_in_head inhead "+   
        " on   insub.in_bill_id = inhead.id "+  
        " left outer join   t_product pro "+   
        " on   insub.product_id = pro.id "+  
        " left outer join   t_provider per "+   
        " on   per.code = inhead.provider_code "+  
        " where "+  
        " 1 = 1 "+
        " and insub.status = '" + InSub.STATUS_AVALIBLE + "'" + 
        " and inhead.warehouse_code  in ("+warehouseCodes+") ";
    if(provider_name != "")
        in += " and per.name like '%"+provider_name+"%' "; 
    if(start != "")
        in += " and inhead.in_time >= '"+start+"' ";
    if(end != ""){
        if(end.length() <= 10) end += " 23:59:59";
        in += " and inhead.in_time <= '"+end+"' ";
    }    
    if(balance_mode != "")
        in += " and insub.balance_mode = '"+balance_mode+"' ";
    if(balance_flag != "")
        in += " and inhead.balance_flag = '"+balance_flag+"' ";
    if(concentricity_code != "")
        in += " and pro.concentricity_code = '"+concentricity_code+"' ";
	if(productName != "")
		in += " and pro.name like '%"+productName+"%' ";
	if(productBrand != "")
		in += " and pro.product_brand like '%"+productBrand+"%' ";
    if(is_normal != "")
        in += " and pro.is_normal = '"+is_normal+"' ";
    out = " select "+  
        " outhead.warehouse_code warehouse_code, "+
        " outsub.product_code product_code, "+  
        " outsub.product_id product_id, "+ 
        " outsub.position_code position_code, "+ 
        " 0 in_count, "+  
        " outsub.product_price price, "+  
        " 0 in_moneys, "+  
        " outsub.product_count out_count, "+  
        " outsub.product_money out_moneys, "+  
        " 0 final_count, "+  
        " 0 final_money "+  
        " from  t_out_sub outsub "+  
        " left outer join   t_out_head outhead "+   
        " on   outsub.out_bill_code = outhead.out_bill_code "+  
        " left outer join   t_product pro "+   
        " on   outsub.product_id = pro.id "+  
        " left outer join   t_provider per "+   
        " on   per.code = pro.provider_code "+  
        " where "+  
        " 1 = 1 "+ 
        " and outsub.status = '" + OutSub.STATUS_AVALIBLE + "'" +
        " and  "+
        " outhead.warehouse_code  in ("+warehouseCodes+") ";
    if(provider_name != "")
        out += " and per.name like '%"+provider_name+"%' ";
    if(start != "")
        out += " and outhead.out_time >= '"+start+"' ";
    if(end != ""){
        if(end.length() <= 10) end += " 23:59:59";
        out += " and outhead.out_time <= '"+end+"' ";
    }    
    if(balance_mode != "")
        out += " and outsub.balance_mode = '"+balance_mode+"' ";
    if(balance_flag != "")
        out += " and outhead.balance_flag = '"+balance_flag+"' ";
    if(concentricity_code != "")
        out += " and pro.concentricity_code = '"+concentricity_code+"' ";
	if(productName != "")
		out += " and pro.name like '%"+productName+"%' ";
	if(productBrand != "")
		out += " and pro.product_brand like '%"+productBrand+"%' ";
    if(is_normal != "")
        out += " and pro.is_normal = '"+is_normal+"' ";
    
   String all = masterStart + in + " union all " + out + masterEnd;
   		System.out.println("sql:"+all);
        try {
            UniversalTable ut = new UniversalTable();
            InOutStatisticSearchClass io = new InOutStatisticSearchClass();
            FirstForm obj = io.selectMoneyForAll(con,event,isFirstVisit,warehouseCodes);
            ut.initial(con, all, new Conditions(), event.getPageSize(), event.getPageNum());
            mReturn = ut.getList();
            mReturn.add(obj);
        } catch (Exception e) {
            System.out.println("InOutStatistic SQL detailqueryGroup error:" + e.getMessage());
        }
        return mReturn;
    }
}
